package com.ai.bitcoin.jpa.dao;

import com.ai.bitcoin.jpa.entity.BccReportMonitorConfig;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.transaction.annotation.Transactional;

import java.math.BigDecimal;
import java.util.List;

/**
 * @description: ${description}
 * @author: gulj
 * @create: 2018-08-09 16:27
 **/
public interface BccReportMonitorConfigDao extends JpaRepository<BccReportMonitorConfig, String> {


    BccReportMonitorConfig findByBccIdAndOpenId(String bccId, String openId);

    @Query(nativeQuery = true, value = " SELECT\n" +
            "\t\t ercTwoZeroSwitchCount,boomBustSwitchCount,exchangeAnnoSwitchCount,coinPriceRemindCount\n" +
            "\tfrom (\n" +
            "\t\t\tSELECT count(1) ercTwoZeroSwitchCount from bcc_report_monitor_config where bcc_id = ?1 and erc_two_zero_switch = 1 \n" +
            "\t) ercTemp\n" +
            "\tLEFT JOIN (\n" +
            "\t\tSELECT count(1) boomBustSwitchCount,1 from bcc_report_monitor_config where bcc_id = ?1 and boom_bust_switch = 1 \n" +
            "\t) boomTemp on 1=1\n" +
            "\tLEFT JOIN (\n" +
            "\t\tSELECT count(1) exchangeAnnoSwitchCount,1 from bcc_report_monitor_config where bcc_id = ?1 and exchange_anno_switch = 1 \n" +
            "\t) exchangeAnoo on 1=1\n" +
            "\tLEFT JOIN (\n" +
            "\t\tSELECT count(1) coinPriceRemindCount,1 from bcc_report_monitor_config where bcc_id = ?1 and coin_price_market is not null\n" +
            "\t) coinMarket on 1=1")
    List<Object[]> queryMonitorUserCountByBccId(String bccId);


    /**
     * 交易所公告(用户已经订阅该币)
     *
     * @param bccId
     * @param openIds
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT * from bcc_report_monitor_config  WHERE bcc_id = ?1 and open_id in (?2) and exchange_anno_switch = ?3")
    List<BccReportMonitorConfig> queryExchangeAnnoSwitchByBccIdAndOpenIdsAndStatus(String bccId, List<String> openIds, Integer status);

    /**
     * 暴涨暴跌开关(用户已经订阅该币)
     *
     * @param bccId
     * @param openIds
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT * from bcc_report_monitor_config  WHERE bcc_id = ?1 and open_id in (?2) and boom_bust_switch = ?3")
    List<BccReportMonitorConfig> queryBoomBustSwitchExchangeAnnoSwitchByBccIdAndOpenIdsAndStatus(String bccId, List<String> openIds, Integer status);

    /**
     * ERC20代币(用户已经订阅该币)
     *
     * @param bccId
     * @param openIds
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT * from bcc_report_monitor_config  WHERE bcc_id = ?1 and open_id in (?2) and erc_two_zero_switch = ?3")
    List<BccReportMonitorConfig> queryErcTwoZeroSwitchExchangeAnnoSwitchByBccIdAndOpenIdsAndStatus(String bccId, List<String> openIds, Integer status);

    /**
     * 报告订阅开关(用户已经订阅该币)
     *
     * @param bccId
     * @param openIds
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT * from bcc_report_monitor_config  WHERE bcc_id = ?1 and open_id in (?2) and report_subscribe_switch = ?3")
    List<BccReportMonitorConfig> queryReportSubscribeSwitchExchangeAnnoSwitchByBccIdAndOpenIdsAndStatus(String bccId, List<String> openIds, Integer status);

    /**
     * 舆情动态开关(用户已经订阅该币)
     *
     * @param bccId
     * @param openIds
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT * from bcc_report_monitor_config  WHERE bcc_id = ?1 and open_id in (?2) and news_dynamic_switch = ?3")
    List<BccReportMonitorConfig> queryNewsDynamicSwitchExchangeAnnoSwitchByBccIdAndOpenIdsAndStatus(String bccId, List<String> openIds, Integer status);


    /**
     * 买点和卖点开关(用户已经订阅该币)
     *
     * @param bccId
     * @param openIds
     * @param status
     * @return
     */
    @Query(nativeQuery = true, value = "SELECT * from bcc_report_monitor_config  WHERE bcc_id = ?1 and open_id in (?2) and buyer_seller_point_switch = ?3")
    List<BccReportMonitorConfig> queryBuyerSellPointSwitchByBccIdAndOpenIdsAndStatus(String bccId, List<String> openIds, Integer status);


    @Query(nativeQuery = true, value = "update bcc_report_monitor_config set coin_price_min = ?2,coin_price_max = ?1,coin_price_rate=?3 ,coin_price_market = ?4 WHERE id = ?5")
    @Modifying
    @Transactional
    void updatePriceAndRate(BigDecimal coinPriceMin, BigDecimal coinPriceMax, Integer coinPriceRate, String coinPriceMarket, String id);

    /**
     * 更新ERC20
     *
     * @param openId       openId
     * @param bccIdList    要更新的bccID
     * @param switchConfig 开关
     */
    @Query(value = "UPDATE BccReportMonitorConfig SET ercTwoZeroSwitch = :switchConfig WHERE openId = :openId AND bccId IN (:bccIdList)")
    @Modifying
    void updateErcTwoZeroByOpenId(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList, @Param("switchConfig") int switchConfig);

    /**
     * 更新暴涨暴跌开关
     *
     * @param openId       openId
     * @param bccIdList    要更新的bccID
     * @param switchConfig 开关
     */
    @Query(value = "UPDATE BccReportMonitorConfig SET boomBustSwitch = :switchConfig WHERE openId = :openId AND bccId IN (:bccIdList)")
    @Modifying
    void updateBoomBustByOpenId(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList, @Param("switchConfig") int switchConfig);

    /**
     * 更新交易所公告开关
     *
     * @param openId       openId
     * @param bccIdList    要更新的bccID
     * @param switchConfig 开关
     */
    @Query(value = "UPDATE BccReportMonitorConfig SET exchangeAnnoSwitch = :switchConfig WHERE openId = :openId AND bccId IN (:bccIdList)")
    @Modifying
    void updateExchangeAnnoByOpenId(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList, @Param("switchConfig") int switchConfig);

    /**
     * 更新买点卖点开关
     *
     * @param openId       openId
     * @param bccIdList    要更新的bccID
     * @param switchConfig 开关
     */
    @Query(value = "UPDATE BccReportMonitorConfig SET buyerSellerPointSwitch = :switchConfig WHERE openId = :openId AND bccId IN (:bccIdList)")
    @Modifying
    void updateBuyerSellerPointByOpenId(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList, @Param("switchConfig") int switchConfig);

    /**
     * 更新订阅开关
     *
     * @param openId       openId
     * @param bccIdList    要更新的bccID
     * @param switchConfig 开关
     */
    @Query(value = "UPDATE BccReportMonitorConfig SET reportSubscribeSwitch = :switchConfig WHERE openId = :openId AND bccId IN (:bccIdList)")
    @Modifying
    void updateReportSubscribeByOpenId(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList, @Param("switchConfig") int switchConfig);

    /**
     * 更新舆情动态开关
     *
     * @param openId       openId
     * @param bccIdList    要更新的bccID
     * @param switchConfig 开关
     */
    @Query(value = "UPDATE BccReportMonitorConfig SET newsDynamicSwitch = :switchConfig WHERE openId = :openId AND bccId IN (:bccIdList)")
    @Modifying
    void updateNewsDynamicByOpenId(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList, @Param("switchConfig") int switchConfig);

    /**
     * 查找参数中用户已配置的bcc列表
     *
     * @param openId    openId
     * @param bccIdList bcc列表
     * @return
     */
    @Query("SELECT e.bccId FROM BccReportMonitorConfig e WHERE e.openId = :openId AND e.bccId IN (:bccIdList)")
    List<String> findHasSetting(@Param("openId") String openId, @Param("bccIdList") List<String> bccIdList);

    /**
     * 取得用戶所有配置
     *
     * @param openId openId
     * @return 配置信息
     */
    List<BccReportMonitorConfig> findByOpenId(String openId);

    /**
     * 满足最低价格的用户配置
     */
    @Query("SELECT e FROM BccReportMonitorConfig e WHERE e.bccId = :bccId AND  e.coinPriceMin > :price")
    List<BccReportMonitorConfig> findSettingMinThreshholdUser(@Param("bccId") String bccId, @Param("price") BigDecimal price);

    /**
     * 满足最高价格的用户配置
     */
    @Query("SELECT e FROM BccReportMonitorConfig e WHERE e.bccId = :bccId AND  e.coinPriceMax < :price")
    List<BccReportMonitorConfig> findSettingMaxThreshholdUser(@Param("bccId") String bccId, @Param("price") BigDecimal price);
}
